import asyncio
import os
from typing import Annotated

import semantic_kernel as sk
from semantic_kernel.functions import KernelArguments, kernel_function

async def run_function(*args):
   return await kernel.invoke(*args)


class DBConnector:
    def __init__(self, db_cursor):
        self.db_cursor = db_cursor

    @kernel_function(
        description="查询数据库",  # function 描述
        name="query_database",  # function 名字
    )
    def exec(self, sql_exp: Annotated[str,"用户要执行的SQL语句"]) -> Annotated[str,"SQL的查询结果"]:
        cursor = self.db_cursor.execute(sql_exp)
        records = cursor.fetchall()
        return str(records)

def initDB():
    # 定义本地函数和数据库

    import sqlite3

    # 创建数据库连接
    conn = sqlite3.connect(':memory:')
    cursor = conn.cursor()

    # 创建orders表
    cursor.execute("""
    CREATE TABLE Courses (
        id INT AUTO_INCREMENT PRIMARY KEY,
        course_date DATE NOT NULL,
        start_time TIME NOT NULL,
        end_time TIME NOT NULL,
        course_name VARCHAR(255) NOT NULL,
        instructor VARCHAR(255) NOT NULL
    );
    """)

    # 插入5条明确的模拟记录
    timetable = [
        ('2024-01-23', '20:00', '22:00', '大模型应用开发基础', '孙志岗'),
        ('2024-01-25', '20:00', '22:00', 'Prompt Engineering', '孙志岗'),
        ('2024-01-29', '20:00', '22:00', '赠课：软件开发基础概念与环境搭建', '西树'),
        ('2024-02-20', '20:00', '22:00', '从AI编程认知AI', '林晓鑫'),
        ('2024-02-22', '20:00', '22:00', 'Function Calling', '孙志岗'),
        ('2024-02-29', '20:00', '22:00', 'RAG和Embeddings', '王卓然'),
        ('2024-03-05', '20:00', '22:00', 'Assistants API', '王卓然'),
        ('2024-03-07', '20:00', '22:00', 'Semantic Kernel', '王卓然'),
        ('2024-03-14', '20:00', '22:00', 'LangChain', '王卓然'),
        ('2024-03-19', '20:00', '22:00', 'LLM应用开发工具链', '王卓然'),
        ('2024-03-21', '20:00', '22:00', '手撕 AutoGPT', '王卓然'),
        ('2024-03-26', '20:00', '22:00', '模型微调（上）', '王卓然'),
        ('2024-03-28', '20:00', '22:00', '模型微调（下）', '王卓然'),
        ('2024-04-09', '20:00', '22:00', '多模态大模型（上）', '多老师'),
        ('2024-04-11', '20:00', '22:00', '多模态大模型（中）', '多老师'),
        ('2024-04-16', '20:00', '22:00', '多模态大模型（下）', '多老师'),
        ('2024-04-18', '20:00', '22:00', 'AI产品部署和交付（上）', '王树冬'),
        ('2024-04-23', '20:00', '22:00', 'AI产品部署和交付（下）', '王树冬'),
        ('2024-04-25', '20:00', '22:00', '抓住大模型时代的创业机遇', '孙志岗'),
        ('2024-05-07', '20:00', '22:00', '产品运营和业务沟通', '孙志岗'),
        ('2024-05-09', '20:00', '22:00', '产品设计', '孙志岗'),
        ('2024-05-14', '20:00', '22:00', '项目方案分析与设计', '王卓然'),
    ]

    for record in timetable:
        cursor.execute('''
        INSERT INTO Courses (course_date, start_time, end_time, course_name, instructor)
        VALUES (?, ?, ?, ?, ?)
        ''', record)

    # 提交事务
    conn.commit()
    return cursor

if __name__=='__main__':
   os.environ["OPENAI_API_KEY"] = os.environ["OPENAI_API_KEY_ZHIHU"]
   os.environ["OPENAI_BASE_URL"] = os.environ["OPENAI_API_BASE_ZHIHU"]

   cursor = initDB()

   kernel = sk.Kernel()
   #注册 function
   myPlugin = kernel.add_plugin(DBConnector(cursor),"DBConnector")
   result = asyncio.run(run_function(myPlugin["query_database"], KernelArguments(sql_exp="SELECT COUNT(*) as count FROM Courses WHERE instructor = '王卓然'")))
   print(result)


   #直接执行
   result = asyncio.run(myPlugin["query_database"](kernel = kernel,sql_exp="SELECT COUNT(*) as count FROM Courses WHERE instructor = '王卓然'"))
   print(result)